﻿/**  版本信息模板在安装目录下，可自行修改。
* ST_Investor.cs
*
* 功 能： N/A
* 类 名： ST_Investor
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2015/1/15 10:26:12   N/A    初版
*
* Copyright (c) 2012 Maticsoft Corporation. All rights reserved.
*┌──────────────────────────────────┐
*│　此技术信息为本公司机密信息，未经本公司书面同意禁止向第三方披露．　│
*│　版权所有：动软卓越（北京）科技有限公司　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using DBUtil;
using Function;
using Model;
using Model.Permission;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
	/// <summary>
	/// 数据访问类:ST_Investor
	/// </summary>
	public partial class ST_InvestorDAL
	{
         DbHelperSQLP DbHelperSQL = new DbHelperSQLP(PubConstant.JHShop);
         public ST_InvestorDAL()
		{}
         #region  BasicMethod
         /// <summary>
         /// 是否存在该记录
         /// </summary>
         public bool Exists(int ID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) from ST_Investor");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             return DbHelperSQL.Exists(strSql.ToString(), parameters);
         }


         /// <summary>
         /// 增加一条数据
         /// </summary>
         public int Add(ST_Investor model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("insert into ST_Investor(");
             strSql.Append("Type,Name,MainContactor,MainTel,UrgentContactor,UrgentTel,Email,LoginPw,PIdentification,PIdentificationURL,Legaler,LIdentification,LIdentificationURL,LicenceNo,LicenceZURL,LicenceFURL,CreateTime,OpID,State,Note,LoginPWChanged)");
             strSql.Append(" values (");
             strSql.Append("@Type,@Name,@MainContactor,@MainTel,@UrgentContactor,@UrgentTel,@Email,@LoginPw,@PIdentification,@PIdentificationURL,@Legaler,@LIdentification,@LIdentificationURL,@LicenceNo,@LicenceZURL,@LicenceFURL,@CreateTime,@OpID,@State,@Note,@LoginPWChanged)");
             strSql.Append(";select @@IDENTITY");
             SqlParameter[] parameters = {
					new SqlParameter("@Type", SqlDbType.TinyInt,1),
					new SqlParameter("@Name", SqlDbType.NVarChar,64),
					new SqlParameter("@MainContactor", SqlDbType.NVarChar,64),
					new SqlParameter("@MainTel", SqlDbType.VarChar,32),
					new SqlParameter("@UrgentContactor", SqlDbType.NVarChar,64),
					new SqlParameter("@UrgentTel", SqlDbType.VarChar,32),
					new SqlParameter("@Email", SqlDbType.VarChar,64),
					new SqlParameter("@LoginPw", SqlDbType.NVarChar,100),
					new SqlParameter("@PIdentification", SqlDbType.VarChar,18),
					new SqlParameter("@PIdentificationURL", SqlDbType.VarChar,256),
					new SqlParameter("@Legaler", SqlDbType.NVarChar,64),
					new SqlParameter("@LIdentification", SqlDbType.VarChar,18),
					new SqlParameter("@LIdentificationURL", SqlDbType.VarChar,256),
					new SqlParameter("@LicenceNo", SqlDbType.VarChar,50),
					new SqlParameter("@LicenceZURL", SqlDbType.VarChar,256),
					new SqlParameter("@LicenceFURL", SqlDbType.VarChar,256),
					new SqlParameter("@CreateTime", SqlDbType.VarChar,19),
					new SqlParameter("@OpID", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NText),
					new SqlParameter("@LoginPWChanged", SqlDbType.TinyInt,1)};
             parameters[0].Value = model.Type;
             parameters[1].Value = model.Name;
             parameters[2].Value = model.MainContactor;
             parameters[3].Value = model.MainTel;
             parameters[4].Value = model.UrgentContactor;
             parameters[5].Value = model.UrgentTel;
             parameters[6].Value = model.Email;
             parameters[7].Value = model.LoginPw;
             parameters[8].Value = model.PIdentification;
             parameters[9].Value = model.PIdentificationURL;
             parameters[10].Value = model.Legaler;
             parameters[11].Value = model.LIdentification;
             parameters[12].Value = model.LIdentificationURL;
             parameters[13].Value = model.LicenceNo;
             parameters[14].Value = model.LicenceZURL;
             parameters[15].Value = model.LicenceFURL;
             parameters[16].Value = model.CreateTime;
             parameters[17].Value = model.OpID;
             parameters[18].Value = model.State;
             parameters[19].Value = model.Note;
             parameters[20].Value = model.LoginPWChanged;

             object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
             if (obj == null)
             {
                 return 0;
             }
             else
             {
                 return Convert.ToInt32(obj);
             }
         }
         /// <summary>
         /// 更新一条数据
         /// </summary>
         public bool Update(ST_Investor model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update ST_Investor set ");
             strSql.Append("Type=@Type,");
             strSql.Append("Name=@Name,");
             strSql.Append("MainContactor=@MainContactor,");
             strSql.Append("MainTel=@MainTel,");
             strSql.Append("UrgentContactor=@UrgentContactor,");
             strSql.Append("UrgentTel=@UrgentTel,");
             strSql.Append("Email=@Email,");
             strSql.Append("LoginPw=@LoginPw,");
             strSql.Append("PIdentification=@PIdentification,");
             strSql.Append("PIdentificationURL=@PIdentificationURL,");
             strSql.Append("Legaler=@Legaler,");
             strSql.Append("LIdentification=@LIdentification,");
             strSql.Append("LIdentificationURL=@LIdentificationURL,");
             strSql.Append("LicenceNo=@LicenceNo,");
             strSql.Append("LicenceZURL=@LicenceZURL,");
             strSql.Append("LicenceFURL=@LicenceFURL,");
             strSql.Append("CreateTime=@CreateTime,");
             strSql.Append("OpID=@OpID,");
             strSql.Append("State=@State,");
             strSql.Append("Note=@Note,");
             strSql.Append("LoginPWChanged=@LoginPWChanged");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@Type", SqlDbType.TinyInt,1),
					new SqlParameter("@Name", SqlDbType.NVarChar,64),
					new SqlParameter("@MainContactor", SqlDbType.NVarChar,64),
					new SqlParameter("@MainTel", SqlDbType.VarChar,32),
					new SqlParameter("@UrgentContactor", SqlDbType.NVarChar,64),
					new SqlParameter("@UrgentTel", SqlDbType.VarChar,32),
					new SqlParameter("@Email", SqlDbType.VarChar,64),
					new SqlParameter("@LoginPw", SqlDbType.NVarChar,100),
					new SqlParameter("@PIdentification", SqlDbType.VarChar,18),
					new SqlParameter("@PIdentificationURL", SqlDbType.VarChar,256),
					new SqlParameter("@Legaler", SqlDbType.NVarChar,64),
					new SqlParameter("@LIdentification", SqlDbType.VarChar,18),
					new SqlParameter("@LIdentificationURL", SqlDbType.VarChar,256),
					new SqlParameter("@LicenceNo", SqlDbType.VarChar,50),
					new SqlParameter("@LicenceZURL", SqlDbType.VarChar,256),
					new SqlParameter("@LicenceFURL", SqlDbType.VarChar,256),
					new SqlParameter("@CreateTime", SqlDbType.VarChar,19),
					new SqlParameter("@OpID", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.TinyInt,1),
					new SqlParameter("@Note", SqlDbType.NText),
					new SqlParameter("@LoginPWChanged", SqlDbType.TinyInt,1),
					new SqlParameter("@ID", SqlDbType.Int,4)};
             parameters[0].Value = model.Type;
             parameters[1].Value = model.Name;
             parameters[2].Value = model.MainContactor;
             parameters[3].Value = model.MainTel;
             parameters[4].Value = model.UrgentContactor;
             parameters[5].Value = model.UrgentTel;
             parameters[6].Value = model.Email;
             parameters[7].Value = model.LoginPw;
             parameters[8].Value = model.PIdentification;
             parameters[9].Value = model.PIdentificationURL;
             parameters[10].Value = model.Legaler;
             parameters[11].Value = model.LIdentification;
             parameters[12].Value = model.LIdentificationURL;
             parameters[13].Value = model.LicenceNo;
             parameters[14].Value = model.LicenceZURL;
             parameters[15].Value = model.LicenceFURL;
             parameters[16].Value = model.CreateTime;
             parameters[17].Value = model.OpID;
             parameters[18].Value = model.State;
             parameters[19].Value = model.Note;
             parameters[20].Value = model.LoginPWChanged;
             parameters[21].Value = model.ID;

             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }

         /// <summary>
         /// 删除一条数据
         /// </summary>
         public bool Delete(int ID)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("delete from ST_Investor ");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
         /// <summary>
         /// 批量删除数据
         /// </summary>
         public bool DeleteList(string IDlist)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("delete from ST_Investor ");
             strSql.Append(" where ID in (" + IDlist + ")  ");
             int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }


         /// <summary>
         /// 得到一个对象实体
         /// </summary>
         public ST_Investor GetModel(int ID)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("select  top 1 ID,Type,Name,MainContactor,MainTel,UrgentContactor,UrgentTel,Email,LoginPw,PIdentification,PIdentificationURL,Legaler,LIdentification,LIdentificationURL,LicenceNo,LicenceZURL,LicenceFURL,CreateTime,OpID,State,Note,LoginPWChanged from ST_Investor ");
             strSql.Append(" where ID=@ID");
             SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
             parameters[0].Value = ID;

             ST_Investor model = new ST_Investor();
             DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
             if (ds.Tables[0].Rows.Count > 0)
             {
                 return DataRowToModel(ds.Tables[0].Rows[0]);
             }
             else
             {
                 return null;
             }
         }


         /// <summary>
         /// 得到一个对象实体
         /// </summary>
         public ST_Investor DataRowToModel(DataRow row)
         {
             ST_Investor model = new ST_Investor();
             if (row != null)
             {
                 if (row["ID"] != null && row["ID"].ToString() != "")
                 {
                     model.ID = int.Parse(row["ID"].ToString());
                 }
                 if (row["Type"] != null && row["Type"].ToString() != "")
                 {
                     model.Type = int.Parse(row["Type"].ToString());
                 }
                 if (row["Name"] != null)
                 {
                     model.Name = row["Name"].ToString();
                 }
                 if (row["MainContactor"] != null)
                 {
                     model.MainContactor = row["MainContactor"].ToString();
                 }
                 if (row["MainTel"] != null)
                 {
                     model.MainTel = row["MainTel"].ToString();
                 }
                 if (row["UrgentContactor"] != null)
                 {
                     model.UrgentContactor = row["UrgentContactor"].ToString();
                 }
                 if (row["UrgentTel"] != null)
                 {
                     model.UrgentTel = row["UrgentTel"].ToString();
                 }
                 if (row["Email"] != null)
                 {
                     model.Email = row["Email"].ToString();
                 }
                 if (row["LoginPw"] != null)
                 {
                     model.LoginPw = row["LoginPw"].ToString();
                 }
                 if (row["PIdentification"] != null)
                 {
                     model.PIdentification = row["PIdentification"].ToString();
                 }
                 if (row["PIdentificationURL"] != null)
                 {
                     model.PIdentificationURL = row["PIdentificationURL"].ToString();
                 }
                 if (row["Legaler"] != null)
                 {
                     model.Legaler = row["Legaler"].ToString();
                 }
                 if (row["LIdentification"] != null)
                 {
                     model.LIdentification = row["LIdentification"].ToString();
                 }
                 if (row["LIdentificationURL"] != null)
                 {
                     model.LIdentificationURL = row["LIdentificationURL"].ToString();
                 }
                 if (row["LicenceNo"] != null)
                 {
                     model.LicenceNo = row["LicenceNo"].ToString();
                 }
                 if (row["LicenceZURL"] != null)
                 {
                     model.LicenceZURL = row["LicenceZURL"].ToString();
                 }
                 if (row["LicenceFURL"] != null)
                 {
                     model.LicenceFURL = row["LicenceFURL"].ToString();
                 }
                 if (row["CreateTime"] != null)
                 {
                     model.CreateTime = row["CreateTime"].ToString();
                 }
                 if (row["OpID"] != null && row["OpID"].ToString() != "")
                 {
                     model.OpID = int.Parse(row["OpID"].ToString());
                 }
                 if (row["State"] != null && row["State"].ToString() != "")
                 {
                     model.State = int.Parse(row["State"].ToString());
                 }
                 if (row["Note"] != null)
                 {
                     model.Note = row["Note"].ToString();
                 }
                 if (row["LoginPWChanged"] != null && row["LoginPWChanged"].ToString() != "")
                 {
                     model.LoginPWChanged = int.Parse(row["LoginPWChanged"].ToString());
                 }
             }
             return model;
         }

         /// <summary>
         /// 获得数据列表
         /// </summary>
         public DataSet GetList(string strWhere)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ID,Type,Name,MainContactor,MainTel,UrgentContactor,UrgentTel,Email,LoginPw,PIdentification,PIdentificationURL,Legaler,LIdentification,LIdentificationURL,LicenceNo,LicenceZURL,LicenceFURL,CreateTime,OpID,State,Note,LoginPWChanged ");
             strSql.Append(" FROM ST_Investor ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             return DbHelperSQL.Query(strSql.ToString());
         }

         /// <summary>
         /// 获得前几行数据
         /// </summary>
         public DataSet GetList(int Top, string strWhere, string filedOrder)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ");
             if (Top > 0)
             {
                 strSql.Append(" top " + Top.ToString());
             }
             strSql.Append(" ID,Type,Name,MainContactor,MainTel,UrgentContactor,UrgentTel,Email,LoginPw,PIdentification,PIdentificationURL,Legaler,LIdentification,LIdentificationURL,LicenceNo,LicenceZURL,LicenceFURL,CreateTime,OpID,State,Note,LoginPWChanged ");
             strSql.Append(" FROM ST_Investor ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             strSql.Append(" order by " + filedOrder);
             return DbHelperSQL.Query(strSql.ToString());
         }

         /// <summary>
         /// 获取记录总数
         /// </summary>
         public int GetRecordCount(string strWhere)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) FROM ST_Investor ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             object obj = DbHelperSQL.GetSingle(strSql.ToString());
             if (obj == null)
             {
                 return 0;
             }
             else
             {
                 return Convert.ToInt32(obj);
             }
         }
         /// <summary>
         /// 分页获取数据列表
         /// </summary>
         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("SELECT * FROM ( ");
             strSql.Append(" SELECT ROW_NUMBER() OVER (");
             if (!string.IsNullOrEmpty(orderby.Trim()))
             {
                 strSql.Append("order by T." + orderby);
             }
             else
             {
                 strSql.Append("order by T.ID desc");
             }
             strSql.Append(")AS Row, T.*  from ST_Investor T ");
             if (!string.IsNullOrEmpty(strWhere.Trim()))
             {
                 strSql.Append(" WHERE " + strWhere);
             }
             strSql.Append(" ) TT");
             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
             return DbHelperSQL.Query(strSql.ToString());
         }


         #endregion  BasicMethod
		#region  MethodEx

        /// <summary>
        /// 根据用户名判断是否存在该记录
        /// </summary>
         public bool ExistsByEmail(string Email)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ST_Investor");
            strSql.Append(" where Email='" + Email + "'");
            return DbHelperSQL.Exists(strSql.ToString());
        }

         /// <summary>
         /// 根据主要联系人电话号码判断是否存在该记录
         /// </summary>
         public bool ExistsByMainTel(string MainTel)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) from ST_Investor");
             strSql.Append(" where MainTel='" + MainTel + "'");
             return DbHelperSQL.Exists(strSql.ToString());
         }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
         public ST_Investor GetModel(string Email, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * ");
            strSql.Append(" from ST_Investor ");
            strSql.Append(" where Email='" + Email + "' and  LoginPw='" + pwd + "'");
            ST_Investor model = new ST_Investor();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据主要联系人电话号码，查询实体
        /// </summary>
        /// <param name="MainTel"></param>
        /// <returns></returns>
         public ST_Investor GetModel(string MainTel)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select  top 1  * ");
             strSql.Append(" from ST_Investor ");
             strSql.Append(" where MainTel='" + MainTel + "'");
             ST_Investor model = new ST_Investor();
             DataSet ds = DbHelperSQL.Query(strSql.ToString());
             if (ds.Tables[0].Rows.Count > 0)
             {
                 return DataRowToModel(ds.Tables[0].Rows[0]);
             }
             else
             {
                 return null;
             }
         }
         /// <summary>
         /// 近三天的收益
         /// </summary>
         public DataTable Get_sy(int shopid, DateTime dt,out string _strMess)
         {
             //var p = new[] {new SqlParameter("@shopID",SqlDbType.Int,4),
             //    new SqlParameter("@dqtime", SqlDbType.DateTime,19) };

             //return DbHelperSQL.RunProcedure("proc_SellInfoByDay_temp", p,"Tabtemp");

             Sql_BaseDAL MySqlBase = new Sql_BaseDAL();

             SqlParameter[] sqlparams = {
                new SqlParameter("@shopid",SqlDbType.Int),
                new SqlParameter("@dqtime",SqlDbType.DateTime)
                };

             sqlparams[0].Value = shopid;
             sqlparams[1].Value = dt;

             MySqlBase.myParameter = sqlparams;
             MySqlBase.SqlConnectionString = PubConstant.GetConnectString(PubConstant.DataBaseTarget.JHShop);
             return MySqlBase.SqlProcedure_DataTable(out _strMess, "proc_SellInfoByDay_temp", true);


            
         }
        /// <summary>
         /// 年月日收益 年type1,月type2，日type3
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
         public DataTable Get_nyr(int type, int shopid, out string _strMess)
         {
             Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
             //var p = new[] { new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@sel_type", SqlDbType.Int, 4) };

             SqlParameter[] sqlparams = {
                new SqlParameter("@shopid",SqlDbType.Int),
                new SqlParameter("@sel_type",SqlDbType.Int)
                };
             sqlparams[0].Value = shopid;
             sqlparams[1].Value = type;

             MySqlBase.myParameter = sqlparams;
             MySqlBase.SqlConnectionString = PubConstant.GetConnectString(PubConstant.DataBaseTarget.JHShop);
             return MySqlBase.SqlProcedure_DataTable(out _strMess, "proc_SellInfoByDay_nyr", true);

         }
		#endregion  MethodEx
	}
}

